package com.hhq.demo.dao.impl;
import java.util.Date;

import com.hhq.demo.bean.Courier;
import com.hhq.demo.bean.User;
import com.hhq.demo.bean.UserList;
import com.hhq.demo.dao.UserDao;
import com.hhq.demo.utils.DateFormatUtil;
import com.hhq.demo.utils.DruidUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author HHQ
 * @date 2021年12月16日
 */
public class UserDaoImpl implements UserDao {

    private static final String UPDATE_LOGIN_TIME_BY_ID = "update user set login_time = ? where id=?";
    private String UPDATE_USERNAME_BY_PHONE = "update user set username=? where phone=?";
    private String CONSOLE_DATA = "select count(id) total_user,count(TO_DAYS(register_time)=TO_DAYS(NOW()) or null) today_register_user from user";
    private String LIST_ALL = "select * from user";
    private String LIST_LIMIT = "select * from user limit ?,?";
    private String FIND_BY_PHONE = "select id,username,phone,password,checked from user where phone=?";
    /**
     * 新增快递员
     * 初始值为0                 注册时登录时间为null
     * id username phone password register_time login_time
     */
    private String INSERT = "insert into user values(null,?,?,?,?,?,?)";
    private String UPDATE = "update user set username=?,password=? where id=?";
    private String UPDATE_AND_PHONE = "update user set username=?,phone=?,password=? where id=?";
    private final String UPDATE_BY_PHONE_CHECKED = "update user set username=?,password=?,checked=? where phone=?";
    private String DELETE_BY_ID = "delete from user where id=?";

    /**
     * 查询控制台需要的数据
     *
     * @return map集合数据
     */
    @Override
    public Map<String, Object> consoleData() {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        HashMap<String, Object> map = new HashMap<>();
        try {
            assert connection != null;
            preparedStatement = connection.prepareStatement(CONSOLE_DATA);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                map.put("totalUser", resultSet.getInt("total_user"));
                map.put("todayRegisterUser", resultSet.getInt("today_register_user"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, resultSet);
        }
        return map;
    }

    /**
     * 查询所有用户信息
     *
     * @return 用户集合
     */
    @Override
    public List<UserList> listAll() {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<UserList> userList = new ArrayList<>();
        try {
            assert connection != null;
            preparedStatement = connection.prepareStatement(LIST_ALL);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                UserList user = new UserList();
                user.setId(resultSet.getInt("id"));
                user.setUsername(resultSet.getString("username"));
                user.setPhone(resultSet.getString("phone"));
                user.setPassword(resultSet.getString("password"));
                user.setRegisterTime(DateFormatUtil.format(resultSet.getDate("register_time")));
                user.setLoginTime(DateFormatUtil.format(resultSet.getDate("login_time")));
                userList.add(user);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, resultSet);
        }
        return userList;
    }

    /**
     * 分页查询用户信息
     *
     * @param offset     起始索引
     * @param pageNumber 每页显示数量
     * @return 用户信息集合
     */
    @Override
    public List<UserList> listLimit(Integer offset, Integer pageNumber) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<UserList> userList = new ArrayList<>();
        try {
            assert connection != null;
            preparedStatement = connection.prepareStatement(LIST_LIMIT);
            preparedStatement.setObject(1,offset);
            preparedStatement.setObject(2,pageNumber);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                UserList user = new UserList();
                user.setId(resultSet.getInt("id"));
                user.setUsername(resultSet.getString("username"));
                user.setPhone(resultSet.getString("phone"));
                user.setPassword(resultSet.getString("password"));
                user.setRegisterTime(DateFormatUtil.format(resultSet.getDate("register_time")));
                user.setLoginTime(DateFormatUtil.format(resultSet.getDate("login_time")));
                userList.add(user);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, resultSet);
        }
        return userList;
    }

    /**
     * 新增用户
     *
     * @param user 用户
     * @return 受影响的行数
     */
    @Override
    public int insert(User user) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            assert connection != null;
            preparedStatement = connection.prepareStatement(INSERT);
            //(null,?,?,?,?,?,?)
            preparedStatement.setObject(1,user.getUsername());
            preparedStatement.setObject(2,user.getPhone());
            preparedStatement.setObject(3,user.getPassword());
            preparedStatement.setInt(4,0);
            preparedStatement.setObject(5,new Date());
            preparedStatement.setObject(6,new Date());
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, null);
        }
        return 0;
    }

    /**
     * 修改用户信息
     *
     * @param id    要修改的用户的id
     * @param user  要修改的用户的信息
     * @return 受影响的行数
     */
    @Override
    public int update(Integer id, User user) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            assert connection != null;
            preparedStatement = connection.prepareStatement(UPDATE);
            preparedStatement.setObject(1, user.getUsername());
            preparedStatement.setObject(2, user.getPassword());
            preparedStatement.setObject(3, id);
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, null);
        }
        return 0;
    }

    /**
     * 修改快递员信息包括手机号
     *
     * @param id      快递员id
     * @param user 快递员信息
     * @return 受影响的行数
     */
    @Override
    public int updateAndPhone(Integer id, User user) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            assert connection != null;
            preparedStatement = connection.prepareStatement(UPDATE_AND_PHONE);
            preparedStatement.setObject(1, user.getUsername());
            preparedStatement.setObject(2, user.getPhone());
            preparedStatement.setObject(3, user.getPassword());
            preparedStatement.setObject(4, id);
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, null);
        }
        return 0;
    }

    /**
     * 根据id删除用户
     *
     * @param id id
     * @return 受影响的行数
     */
    @Override
    public int deleteById(Integer id) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            assert connection != null;
            preparedStatement = connection.prepareStatement(DELETE_BY_ID);
            preparedStatement.setObject(1,id);
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, null);
        }
        return 0;
    }

    /**
     * 根据手机号查询用户信息
     *
     * @param phone 用户手机号
     * @return 用户信息
     */
    @Override
    public User findByPhone(String phone) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        User user = new User();
        try {
            assert connection != null;
            preparedStatement = connection.prepareStatement(FIND_BY_PHONE);
            preparedStatement.setObject(1,phone);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                user.setId(resultSet.getInt("id"));
                user.setUsername(resultSet.getString("username"));
                user.setPhone(resultSet.getString("phone"));
                user.setPassword(resultSet.getString("password"));
                user.setChecked(resultSet.getInt("checked"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, resultSet);
        }
        return user;
    }

    /**
     * 根据手机号码认证并修改用户信息
     *
     * @param user 用户信息
     * @return 受影响的行数
     */
    @Override
    public int updateByPhone(User user) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(UPDATE_BY_PHONE_CHECKED);
            preparedStatement.setObject(1,user.getUsername());
            preparedStatement.setObject(2,user.getPassword());
            preparedStatement.setObject(3,1);
            preparedStatement.setObject(4,user.getPhone());
            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,null);
        }
        return 0;
    }

    /**
     * 根据旧手机号码修改用户名
     *
     * @param phone    旧手机号码
     * @param username 要修改的用户信息
     * @return 受影响的行数
     */
    @Override
    public int updateUsername(String phone, String username) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(UPDATE_USERNAME_BY_PHONE);
            preparedStatement.setObject(1,username);
            preparedStatement.setObject(2,phone);
            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,null);
        }
        return 0;
    }

    /**
     * 修改登录时间
     *
     * @param id id
     */
    @Override
    public void updateLoginTimeById(Integer id) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(UPDATE_LOGIN_TIME_BY_ID);
            preparedStatement.setObject(1,new Date());
            preparedStatement.setObject(2,id);
            preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,null);
        }
    }
}
